********************************************************************************
*Project:  	Swiss Trade During the Covid-19 Pandemic: An Early Appraisal	
*Authors:	KB, SL, VP, PW											
*This File: Validate Weekly Trade Data
*Version: 	vFinal						
*Date: 		2020-09-01						
********************************************************************************

********************************************************************************
*1. Import Monthly & Yearly Data
********************************************************************************

*Import Yearly Trade Data 2002-2011
import excel "${root}\data\FCA_Aggregate_Statistics\EZV_Importe-Exporte_jaehrlich_2002-2020.xlsx",  ///
sheet("Tabelle") firstrow case(lower) clear

rename a jahr
rename c import_kg
rename d import_chf
rename e export_kg
rename f export_chf

drop b 

drop if export_chf==""
drop if jahr==""

destring jahr, replace 
destring import_kg, replace
destring import_chf, replace
destring export_kg, replace
destring export_chf, replace

format import* export* %14.0f

sort jahr
order jahr import_kg import_chf export_kg import_chf
compress
saveold "${root}\temp\fca_yearly_trade_2002_2020.dta", replace version(13)

*Import Monthly Trade Data 2002-2011
import excel "${root}\data\FCA_Aggregate_Statistics\EZV_Importe-Exporte_monatlich_2002-2020.xlsx", 		///
sheet("Tabelle") firstrow case(lower) clear

rename a jahr_monat
rename c import_kg
rename d import_chf
rename e export_kg
rename f export_chf

drop b 

drop if export_chf==""
drop if jahr_monat==""

destring import_kg, replace
destring import_chf, replace
destring export_kg, replace
destring export_chf, replace

format import* export* %14.0f

split jahr_monat, parse(" ")
drop jahr_monat
local i=0
foreach m in Januar Februar März April Mai Juni Juli August September Oktober November Dezember {
	local i=`i'+1
	replace jahr_monat1="`i'" if jahr_monat1=="`m'"
}

replace jahr_monat2=subinstr(jahr_monat2, "*", "",.)
destring jahr_monat1, gen(monat)
destring jahr_monat2, gen(jahr)

drop jahr_monat*

sort jahr
order jahr monat import_kg import_chf export_kg export_chf
compress
saveold "${root}\temp\fca_monthly_trade_2002_2020.dta", replace version(13)


*Trade 2019 by Partner
import excel "${root}\data\FCA_Aggregate_Statistics\EZV_Trade_2019_byPartner.xlsx", 		///
sheet("Tabelle") firstrow case(lower) clear

rename c land_str
rename d import_chf
rename e export_chf

drop a b f
drop if land_str==""
drop if land_str=="Handelspartner"
drop if land_str=="Gesamthandel"

destring import_chf, replace
destring export_chf, replace 

format import* export* %14.0f

replace land_str=lower(stritrim(strltrim(strrtrim(land_str))))
replace land_str="vereinigte staaten" if land_str=="usa"
replace land_str="iran, islamische republik" if land_str=="iran"
replace land_str="russische föderation" if land_str=="russland"


sort land_str
gen id_U=_n

order land_str import_chf export_chf
compress

saveold "${root}\temp\fca_2019_country.dta", replace version(13)

********************************************************************************
*2. Prepare data for comparison
********************************************************************************

*Longformat
***********

*Monthly Data
use "${root}\temp\trade_2002_2020_analysis_temp.dta", clear

keep if waren_art_str=="total" & land_str=="world"

gen monat=month(datum_do)

collapse (sum) handel_chf_mia handel_tonnen, by(jahr monat handel_typ)

sort jahr monat
gen id=_n

reshape wide handel_chf_mia handel_tonnen, i(id) j(handel_typ) string

collapse (sum) handel_chf_mia* handel_tonnen*, by(jahr monat)

rename handel_chf_miaI import_chf_mia 
rename handel_tonnenI import_tonnen
rename handel_chf_miaE export_chf_mia
rename handel_tonnenE export_tonnen

gen double import_chf_CHECK=import_chf_mia*1000*1000*1000
gen double export_chf_CHECK=export_chf_mia*1000*1000*1000
drop *mia

gen double import_kg_CHECK=import_tonnen*1000
gen double export_kg_CHECK=export_tonnen*1000
drop *tonnen

format import* export* %14.0f

sort jahr monat
order jahr monat import_kg import_chf export_kg export_chf
compress
saveold "${root}\temp\fca_monthly_trade_2002_2020_checkme.dta", replace version(13)

*Yearly Data
collapse (sum) import_* export_*, by(jahr)
sort jahr 
order jahr  import_kg import_chf export_kg export_chf
compress
saveold "${root}\temp\fca_yearly_trade_2002_2020_checkme.dta", replace version(13)

*2019 by Handelspartner
use "${root}\temp\trade_2002_2020_analysis_temp.dta", clear
keep if jahr==2019

drop if waren_art_str=="total" & land_str=="world"
drop if land_id>1000

collapse (sum) handel_chf handel_chf_mio handel_chf_mia, by(jahr handel_typ land_id land_str land_iso2)

reshape wide handel_chf handel_chf_mio handel_chf_mia, i(land_id) j(handel_typ) string

rename handel_chf*I import_chf*
rename handel_chf*E export_chf*

foreach v in  chf chf_mio chf_mia {
	foreach t in export import {
		replace `t'_`v'=0 if `t'_`v'==.
		format `t'_`v' %14.3f
	}
}

rename export* export*_CHECK
rename import* import*_CHECK

sort jahr land_id
order jahr land_id land_str land_iso2 import* export*
compress
saveold "${root}\temp\fca_2019_trade_checkme.dta", replace version(13)


*Wideformat
***********
*Monthly Data
use "${root}\temp\trade_2002_2020_analysis_wide_temp.dta", clear

keep if waren_art_str=="total" & land_str=="world"

gen monat=month(datum_do)

collapse (sum) *_chf_mia *_tonnen, by(jahr monat)

gen double import_chf_WIDE=import_chf_mia*1000*1000*1000
gen double export_chf_WIDE=export_chf_mia*1000*1000*1000
drop *mia

gen double import_kg_WIDE=import_tonnen*1000
gen double export_kg_WIDE=export_tonnen*1000
drop *tonnen

format import* export* %14.0f

sort jahr monat
order jahr monat import_kg import_chf export_kg export_chf
compress
saveold "${root}\temp\fca_monthly_trade_2002_2020_checkmeW.dta", replace version(13)

*Yearly Data
collapse (sum) import_* export_*, by(jahr)
sort jahr 
order jahr  import_kg import_chf export_kg export_chf
compress
saveold "${root}\temp\fca_yearly_trade_2002_2020_checkmeW.dta", replace version(13)

*2019 by Handelspartner
use "${root}\temp\trade_2002_2020_analysis_wide_temp.dta", clear
keep if jahr==2019
drop if waren_art_str=="total"
drop if land_id>1000

collapse (sum) import_* export_*, by(jahr land_id land_str land_iso2)

rename import* import*_WIDE
rename export* export*_WIDE

sort land_id
compress
saveold "${root}\temp\fca_2019_trade_checkmeW.dta", replace version(13)


********************************************************************************
*3. Validate data
********************************************************************************

*Longformat
***********

*Yearly Data
use "${root}\temp\fca_yearly_trade_2002_2020_checkme.dta", clear

merge 1:1 jahr using "${root}\temp\fca_yearly_trade_2002_2020.dta"
keep if _merge==3 /*2020 only in master data*/
drop _merge

gen double diff_import_chf=import_chf_CHECK-import_chf
gen double diff_export_chf=export_chf_CHECK-export_chf
gen double diff_import_kg=import_kg_CHECK-import_kg
gen double diff_export_kg=export_kg_CHECK-export_kg

gen double diff_import_chf_pct=(import_chf_CHECK-import_chf)/import_chf_CHECK
gen double diff_export_chf_pct=(export_chf_CHECK-export_chf)/export_chf_CHECK
gen double diff_import_kg_pct=(import_kg_CHECK-import_kg)/import_kg_CHECK
gen double diff_export_kg_pct=(export_kg_CHECK-export_kg)/export_kg_CHECK

format diff* %14.1f
format diff*_pct %9.0g

sum diff_import_chf,d
global YL_I_CHF_MIN_DIFF=`r(min)'
global YL_I_CHF_MAX_DIFF=`r(max)'

sum diff_import_kg,d
global YL_I_KG_MIN_DIFF=`r(min)'
global YL_I_KG_MAX_DIFF=`r(max)'

sum diff_export_chf,d
global YL_E_CHF_MIN_DIFF=`r(min)'
global YL_E_CHF_MAX_DIFF=`r(max)'

sum diff_export_kg,d
global YL_E_KG_MIN_DIFF=`r(min)'
global YL_E_KG_MAX_DIFF=`r(max)'

sum diff_import_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_import_kg_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_kg_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

/*
foreach v in import_chf_CHECK import_chf export_chf_CHECK  export_chf {
	gen `v'_m=`v'/(1000*1000*1000)
}
br jahr import_chf_CHECK_m import_chf_m export_chf_CHECK_m export_chf_m
*/

*Monthly Data
use "${root}\temp\fca_monthly_trade_2002_2020_checkme.dta", clear

merge 1:1 jahr monat using "${root}\temp\fca_monthly_trade_2002_2020.dta"
keep if _merge==3 /*May 2020 only in master data*/
drop _merge

gen double diff_import_chf=import_chf_CHECK-import_chf
gen double diff_export_chf=export_chf_CHECK-export_chf
gen double diff_import_kg=import_kg_CHECK-import_kg
gen double diff_export_kg=export_kg_CHECK-export_kg

gen double diff_import_chf_pct=(import_chf_CHECK-import_chf)/import_chf_CHECK
gen double diff_export_chf_pct=(export_chf_CHECK-export_chf)/export_chf_CHECK
gen double diff_import_kg_pct=(import_kg_CHECK-import_kg)/import_kg_CHECK
gen double diff_export_kg_pct=(export_kg_CHECK-export_kg)/export_kg_CHECK

format diff* %14.1f
format diff*_pct %9.0g

sum diff_import_chf,d
global ML_I_CHF_MIN_DIFF=`r(min)'
global ML_I_CHF_MAX_DIFF=`r(max)'

sum diff_import_kg,d
global ML_I_KG_MIN_DIFF=`r(min)'
global ML_I_KG_MAX_DIFF=`r(max)'

sum diff_export_chf,d
global ML_E_CHF_MIN_DIFF=`r(min)'
global ML_E_CHF_MAX_DIFF=`r(max)'

sum diff_export_kg,d
global ML_E_KG_MIN_DIFF=`r(min)'
global ML_E_KG_MAX_DIFF=`r(max)'

sum diff_import_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_import_kg_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_kg_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

foreach v in import_chf_CHECK  import_chf export_chf_CHECK export_chf {
	gen `v'_m=`v'/(1000*1000*1000)
}

collapse (sum) import_chf_CHECK_m import_chf_m export_chf_CHECK_m  export_chf_m, by(jahr)

gen diff_i=import_chf_CHECK_m-import_chf_m
gen diff_e=export_chf_CHECK_m-export_chf_m

sum diff_i,d
global MYL_I_CHFMIA_MIN_DIFF=`r(min)'
global MYL_I_CHFMIA_MAX_DIFF=`r(max)'

sum diff_e,d
global MYL_E_CHFMIA_MIN_DIFF=`r(min)'
global MYL_E_CHFMIA_MAX_DIFF=`r(max)'

*2019 by Handelspartner 
use "${root}\temp\fca_2019_trade_checkme.dta", clear

replace land_str=lower(stritrim(strltrim(strrtrim(land_str))))
sort land_str
gen id_M=_n

replace land_str="korea (süd)" if land_iso2=="KR"
replace land_str="korea (nord)" if land_iso2=="KP"

reclink2 land_str using "${root}\temp\fca_2019_country.dta", idmaster(id_M) idusing(id_U) gen(m_qual)
sort m_qual

drop if m_qual==. /*A few marginal countries cannot be matched*/
drop m_qual

gen double diff_import_chf=import_chf_CHECK-import_chf
gen double diff_export_chf=export_chf_CHECK-export_chf

gen double diff_import_chf_pct=(import_chf_CHECK-import_chf)/import_chf_CHECK
gen double diff_export_chf_pct=(export_chf_CHECK-export_chf)/export_chf_CHECK

sum diff_import_chf,d
global YL_I_CHF_19HP_MIN_DIFF=`r(min)'
global YL_I_CHF_19HP_MAX_DIFF=`r(max)'

sum diff_export_chf,d
global YL_E_CHF_19HP_MIN_DIFF=`r(min)'
global YL_E_CHF_19HP_MAX_DIFF=`r(max)'

sum diff_import_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001


*Wideformat
***********
*LONG vs WIDE DATENSATZ: YEARLY DATA
use "${root}\temp\fca_yearly_trade_2002_2020_checkmeW.dta", clear

merge 1:1 jahr using "${root}\temp\fca_yearly_trade_2002_2020_checkme.dta"
keep if _merge==3 
drop _merge

gen double diff_import_chf=import_chf_WIDE-import_chf_CHECK
gen double diff_export_chf=export_chf_WIDE-export_chf_CHECK
gen double diff_import_kg=import_kg_WIDE-import_kg_CHECK
gen double diff_export_kg=export_kg_WIDE-export_kg_CHECK

gen double diff_import_chf_pct=(import_chf_WIDE-import_chf_CHECK)/import_chf_WIDE
gen double diff_export_chf_pct=(export_chf_WIDE-export_chf_CHECK)/export_chf_WIDE
gen double diff_import_kg_pct=(import_kg_WIDE-import_kg_CHECK)/import_kg_WIDE
gen double diff_export_kg_pct=(export_kg_WIDE-export_kg_CHECK)/export_kg_WIDE

format diff* %14.1f
format diff*_pct %9.0g

sum diff_import_chf,d
global YLW_I_CHF_MIN_DIFF=`r(min)'
global YLW_I_CHF_MAX_DIFF=`r(max)'

sum diff_import_kg,d
global YLW_I_KG_MIN_DIFF=`r(min)'
global YLW_I_KG_MAX_DIFF=`r(max)'

sum diff_export_chf,d
global YLW_E_CHF_MIN_DIFF=`r(min)'
global YLW_E_CHF_MAX_DIFF=`r(max)'

sum diff_export_kg,d
global YLW_E_KG_MIN_DIFF=`r(min)'
global YLW_E_KG_MAX_DIFF=`r(max)'

sum diff_import_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_import_kg_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_kg_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001


*LONG VS WIDE DATENSATZ: MONTHLY DATA
use "${root}\temp\fca_monthly_trade_2002_2020_checkmeW.dta", clear

merge 1:1 jahr monat using "${root}\temp\fca_monthly_trade_2002_2020_checkme.dta"
keep if _merge==3 
drop _merge

gen double diff_import_chf=import_chf_WIDE-import_chf_CHECK
gen double diff_export_chf=export_chf_WIDE-export_chf_CHECK
gen double diff_import_kg=import_kg_WIDE-import_kg_CHECK
gen double diff_export_kg=export_kg_WIDE-export_kg_CHECK

gen double diff_import_chf_pct=(import_chf_WIDE-import_chf_CHECK)/import_chf_WIDE
gen double diff_export_chf_pct=(export_chf_WIDE-export_chf_CHECK)/export_chf_WIDE
gen double diff_import_kg_pct=(import_kg_WIDE-import_kg_CHECK)/import_kg_WIDE
gen double diff_export_kg_pct=(export_kg_WIDE-export_kg_CHECK)/export_kg_WIDE

format diff* %14.1f
format diff*_pct %9.0g

sum diff_import_chf,d
global MLW_I_CHF_MIN_DIFF=`r(min)'
global MLW_I_CHF_MAX_DIFF=`r(max)'

sum diff_import_kg,d
global MLW_I_KG_MIN_DIFF=`r(min)'
global MLW_I_KG_MAX_DIFF=`r(max)'

sum diff_export_chf,d
global MLW_E_CHF_MIN_DIFF=`r(min)'
global MLW_E_CHF_MAX_DIFF=`r(max)'

sum diff_export_kg,d
global MLW_E_KG_MIN_DIFF=`r(min)'
global MLW_E_KG_MAX_DIFF=`r(max)'

sum diff_import_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_import_kg_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_kg_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001


*LONG VS WIDE DATENSATZ: 2019 by Handelspartner 
use "${root}\temp\fca_2019_trade_checkmeW.dta", clear

merge 1:1 land_iso2 using "${root}\temp\fca_2019_trade_checkme"
keep if _merge==3 
drop _merge

gen double import_chf_WIDE=import_chf_mio_WIDE*1000*1000
gen double export_chf_WIDE=export_chf_mio_WIDE*1000*1000

gen double diff_import_chf=import_chf_WIDE-import_chf_CHECK
gen double diff_export_chf=export_chf_WIDE-export_chf_CHECK

gen double diff_import_chf_pct=(import_chf_WIDE-import_chf_CHECK)/import_chf_WIDE
gen double diff_export_chf_pct=(export_chf_WIDE-export_chf_CHECK)/export_chf_WIDE

sum diff_import_chf,d
global YLW_I_CHF_19HP_MIN_DIFF=`r(min)'
global YLW_I_CHF_19HP_MAX_DIFF=`r(max)'

sum diff_export_chf,d
global YLW_E_CHF_19HP_MIN_DIFF=`r(min)'
global YLW_E_CHF_19HP_MAX_DIFF=`r(max)'

sum diff_import_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001

sum diff_export_chf_pct,d
assert `r(min)'>-0.00001
assert `r(max)'< 0.00001


********************************************************************************
*4.Save checked dataset
********************************************************************************

use "${root}\temp\trade_2002_2020_analysis_temp.dta", clear
save "${root}\workfiles\trade_2002_2020_analysis", replace

use "${root}\temp\trade_2002_2020_analysis_wide_temp.dta", clear
save "${root}\workfiles\trade_2002_2020_analysis_wide.dta",replace

